--    Program: 		CAPARIMP_OM_SYSTEM_PARAM.sql
--    Description:  Import AR OM System Parameter information 
--    Revision:     1st     
--    Parameter: 	

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
                    		                 		
   cursor cur_system_param is
      select * from CRST_OE_SYSTEM_PARAMETERS_ALL;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_system_param loop   	
     l_err_msg_tmp := ''; 
   	 
   	 begin 
       -- import data
       insert into OE_SYSTEM_PARAMETERS_ALL 
       (
         ORG_ID                        ,                  
         CREATION_DATE                 ,                                        
         CREATED_BY                    ,                                        
         LAST_UPDATE_DATE              ,                                        
         LAST_UPDATED_BY               ,                                        
         LAST_UPDATE_LOGIN             ,                                        
         MASTER_ORGANIZATION_ID        ,                                        
         CONTEXT                       ,                                        
         ATTRIBUTE1                    ,                                        
         ATTRIBUTE2                    ,                                        
         ATTRIBUTE3                    ,                                        
         ATTRIBUTE4                    ,                                        
         ATTRIBUTE5                    ,                                        
         ATTRIBUTE6                    ,                                        
         ATTRIBUTE7                    ,                                        
         ATTRIBUTE8                    ,                                        
         ATTRIBUTE9                    ,                                        
         ATTRIBUTE10                   ,                                        
         ATTRIBUTE11                   ,                                        
         ATTRIBUTE12                   ,                                        
         ATTRIBUTE13                   ,                                        
         ATTRIBUTE14                   ,                                        
         ATTRIBUTE15                   ,                                        
         CUSTOMER_RELATIONSHIPS_FLAG   ,                                        
         PROCESS_ORDERS_SORTED_BY      ,                                        
         OE_BALANCE_CONV_TYPE          ,                                        
         OE_BALANCE_CONV_DATE          ,                                        
         AR_BALANCE_CONV_TYPE          ,                                        
         AR_BALANCE_CONV_DATE          ,                                        
         CREDIT_HOLD_LEVEL_CODE        ,                                        
         AUDIT_TRAIL_ENABLE_FLAG       ,                                        
         COMPUTE_MARGIN_FLAG           ,                                        
         FREIGHT_RATING_ENABLED_FLAG   ,                                        
         FTE_SHIP_METHOD_ENABLED_FLAG
       )
       values
       (
         CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),                  
         cur.CREATION_DATE                 ,                                        
         CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),                                        
         cur.LAST_UPDATE_DATE              ,                                        
         CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),                                        
         cur.LAST_UPDATE_LOGIN             ,                                        
         CAPBRIMP_MAPPING.GetOrganizationMapping(cur.MASTER_ORGANIZATION_ID),                                        
         cur.CONTEXT                       ,                                        
         cur.ATTRIBUTE1                    ,                                        
         cur.ATTRIBUTE2                    ,                                        
         cur.ATTRIBUTE3                    ,                                        
         cur.ATTRIBUTE4                    ,                                        
         cur.ATTRIBUTE5                    ,                                        
         cur.ATTRIBUTE6                    ,                                        
         cur.ATTRIBUTE7                    ,                                        
         cur.ATTRIBUTE8                    ,                                        
         cur.ATTRIBUTE9                    ,                                        
         cur.ATTRIBUTE10                   ,                                        
         cur.ATTRIBUTE11                   ,                                        
         cur.ATTRIBUTE12                   ,                                        
         cur.ATTRIBUTE13                   ,                                        
         cur.ATTRIBUTE14                   ,                                        
         cur.ATTRIBUTE15                   ,                                        
         cur.CUSTOMER_RELATIONSHIPS_FLAG   ,                                        
         cur.PROCESS_ORDERS_SORTED_BY      ,                                        
         cur.OE_BALANCE_CONV_TYPE          ,                                        
         cur.OE_BALANCE_CONV_DATE          ,                                        
         cur.AR_BALANCE_CONV_TYPE          ,                                        
         cur.AR_BALANCE_CONV_DATE          ,                                        
         cur.CREDIT_HOLD_LEVEL_CODE        ,                                        
         cur.AUDIT_TRAIL_ENABLE_FLAG       ,                                        
         cur.COMPUTE_MARGIN_FLAG           ,                                        
         cur.FREIGHT_RATING_ENABLED_FLAG   ,                                        
         cur.FTE_SHIP_METHOD_ENABLED_FLAG
       );
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  	  
   end loop;
   
   if l_rejected_count <> 0 then
   	 dbms_output.put_line('1');
   	 commit;
   else
     rollback;
   end if;    
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPARIMP_OM_SYSTEM_PARAM', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit